package Donnees;

import Metier.Objets.LotVitrine;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

public class DaoLotVitrine {

    /** Ajoute un lotVitrine en base
     * @param lot lotVitrine à ajouter en base
     * @return lotVitrine ajouté en base
     * @throws SQLException
     */
    public LotVitrine ajouter(LotVitrine lot) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = ConnectionFactory.getConnection();
            String query = "INSERT INTO LOT_VITRINE (nom, quantite, date_peremption, PRODUIT_ID) "
                    + " VALUES (?, ?, ?, ?)";

            //Préparation de la requête
            preparedStatement = connection.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, lot.getNom());
            preparedStatement.setInt(2, lot.getQuantite());
            preparedStatement.setTimestamp(3, lot.getDatePeremption());
            preparedStatement.setInt(4, lot.getProduit().getId());

            preparedStatement.executeUpdate();
            try (ResultSet generatedKeys = preparedStatement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    lot.setId(generatedKeys.getInt(1));
                }
                else {
                    throw new SQLException("Creating LotVitrine failed, no ID obtained.");
                }
            }
        }
        finally{
            DbUtil.close(preparedStatement);
            DbUtil.close(connection);
        }
        return lot;
    }

    /** Renvoie tous les lotVitrine présents en base
     * @return Liste des lots en vitrine
     * @throws SQLException
     */
    public List<LotVitrine> chercher() throws SQLException {
        Connection connection = null;
        Statement statement = null;
        String query = "SELECT * FROM LOT_VITRINE";
        ResultSet rs = null;
        List<LotVitrine> liste = new ArrayList<>();
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(query);
            while (rs.next()) {
                liste.add(new LotVitrine(rs.getInt("id"),
                        rs.getString("nom"),
                        rs.getInt("quantite"),
                        rs.getTimestamp("date_peremption"),
                        new DaoProduit().chercher(rs.getInt("PRODUIT_id")))
                );
            }
        } finally {
            DbUtil.close(rs);
            DbUtil.close(statement);
            DbUtil.close(connection);
        }
        return liste;
    }

    /** Renvoie le lotVitrine d'id id
     * @param id id du lot à rechercher
     * @return lotVitrine d'id id
     * @throws SQLException
     */
    public LotVitrine chercher(int id) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        String query = "SELECT * FROM LOT_VITRINE WHERE id=" + id;
        ResultSet rs = null;
        LotVitrine lot = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.createStatement();
            rs = statement.executeQuery(query);
            if (rs.next()) {
                lot = new LotVitrine(rs.getInt("id"),
                        rs.getString("nom"),
                        rs.getInt("quantite"),
                        rs.getTimestamp("date_peremption"),
                        new DaoProduit().chercher(rs.getInt("PRODUIT_id")));
            }
        } finally {
            DbUtil.close(rs);
            DbUtil.close(statement);
            DbUtil.close(connection);
        }
        return lot;
    }

    /**
     * supprime un lot de la vitrine en base
     * @param lot
     * @throws java.sql.SQLException
     */
    public void supprimer(LotVitrine lot) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        try {
            String query = "DELETE FROM LOT_VITRINE where id=" + lot.getId();
            connection = ConnectionFactory.getConnection();
            statement = connection.createStatement();
            statement.execute(query);
        } finally {
            DbUtil.close(statement);
            DbUtil.close(connection);
        }
    }
    
    /**
     * diminue la quantite d'un lot de la vitrine en base
     * @param lot
     * @throws java.sql.SQLException
     */
    public void modifier(LotVitrine lot) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try{
            connection = ConnectionFactory.getConnection();
            String query = "UPDATE LOT_VITRINE SET PRODUIT_id = ?, nom = ?, quantite = ?, date_peremption = ?"
                    + " where id = ?";

            preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(1, lot.getProduit().getId());
            preparedStatement.setString(2, lot.getNom());
            preparedStatement.setInt(3, lot.getQuantite());
            preparedStatement.setTimestamp(4, lot.getDatePeremption());
            preparedStatement.setInt(5, lot.getId());

            preparedStatement.executeUpdate();
        }
        finally{
            DbUtil.close(preparedStatement);
            DbUtil.close(connection);
        }
    }
    
}
